﻿using System;
using Dapper;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    internal static partial class SqlTemplate
    {
        internal static class ExtraTemplate
        {
            #region HelpQuestion
            internal static SqlBuilder.Template CreateHelpQuestion(HelpQuestion o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.ExtraSchema.Table.HelpQuestion)
                .Field("([Title],[Content],[Category],[DisplayOrder],[ShowOnMenu],[CreatedBy],[CreatedOn])VALUES(@Title,@Content,@Category,@DisplayOrder,@ShowOnMenu,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Title, o.Content, o.Category, o.DisplayOrder, o.ShowOnMenu, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateHelpQuestion(HelpQuestion o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.ExtraSchema.Table.HelpQuestion)
                    .Field("[Title]=@Title", new { o.Title })
                    .Field("[Content]=@Content", new { o.Content })
                    .Field("[Category]=@Category", new { o.Category })
                    .Field("[DisplayOrder]=@DisplayOrder", new { o.DisplayOrder })
                    .Field("[ShowOnMenu]=@ShowOnMenu", new { o.ShowOnMenu })
                    .Where("[Id] = @Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteHelpQuestion(int id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.ExtraSchema.Table.HelpQuestion)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetHelpQuestion(int id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.ExtraSchema.Table.HelpQuestion)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetAllHelpQuestion()
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.ExtraSchema.Table.HelpQuestion);

                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template GetPagedHelpQuestion(int index = 1, int size = 20, string title = null, byte? category = null, bool? showOnMenu = null)
            {
                CheckIndexSize(ref index, ref size);

                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.ExtraSchema.Table.HelpQuestion)
                .OrderBy("Category,DisplayOrder")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
                if (!string.IsNullOrEmpty(title))
                {
                    builder.Where("[Title] LIKE @Title", new { title = title + "%" });
                }
                if (category.HasValue && category.Value > 0)
                {
                    builder.Where("[Category] = @Category", new { category });
                }
                if (showOnMenu.HasValue)
                {
                    builder.Where("[ShowOnMenu]=@ShowOnMenu", new { showOnMenu });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }
            #endregion HelpQuestion

            #region RegisterLog
            internal static SqlBuilder.Template CreateRegisterLog(RegisterLog o)
            {
                var builder = new SqlBuilder()
                    .Field("([Id],[Origin],[UserName],[Password],[UserAgent],[Browser],[IpAddress],[UrlReferrer],[TraceCode],[CreatedBy],[CreatedOn])VALUES(@Id,@Origin,@UserName,@Password,@UserAgent,@Browser,@IpAddress,@UrlReferrer,@TraceCode,@CreatedBy,@CreatedOn)")
                    .Table(DatabaseSchema.ExtraSchema.Table.RegisterLog)
                    .AddParameters(new { o.Id, o.Origin, o.UserName, o.Password, o.UserAgent, o.Browser, o.IpAddress, o.UrlReferrer, o.TraceCode, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template GetRegisterLog(Guid id)
            {
                var builder = new SqlBuilder()
                   .Field("*")
                   .Table(DatabaseSchema.ExtraSchema.Table.RegisterLog)
                   .Where("[Id] = @Id", new { id })
                   .Where("[CreatedOn]>dateadd(n,-30,getdate())")
                   .OrderBy("CreatedOn DESC");
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }
            #endregion

            #region EmailValidateLog
            internal static SqlBuilder.Template CreateEmailValidateLog(EmailValidateLog o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.ExtraSchema.Table.EmailValidateLog)
                .Field("([Id],[UserId],[Category],[Email],[AuthCode],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@Category,@Email,@AuthCode,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Id, o.UserId, o.Category, o.Email, o.AuthCode, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template DeleteEmailValidateLog(int id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.ExtraSchema.Table.EmailValidateLog)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetEmailValidateLog(string authCode, int? category = null, long? userId = null)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.ExtraSchema.Table.EmailValidateLog)
                    .Where("[CreatedOn]>dateadd(n,-30,getdate())")
                    .Where("[AuthCode] = @AuthCode", new {authCode})
                    .OrderBy("CreatedOn DESC");
                if (category.HasValue)
                {
                    builder.Where("[Category]=@Category", new {category});
                }
                if (userId.HasValue)
                {
                    builder.Where("[UserId]=@UserId", new { userId });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetPagedEmailValidateLog(int index = 1, int size = 20, string email = null, int? category = null, DateTime? beginDate = null, DateTime? endDate = null)
            {
                CheckIndexSize(ref index, ref size);
                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.ExtraSchema.Table.EmailValidateLog)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
                /*
                .Where("");
                */
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }
            #endregion

            #region SmsValidateLog
            internal static SqlBuilder.Template CreateSmsValidateLog(SmsValidateLog o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.ExtraSchema.Table.SmsValidateLog)
                .Field("([Id],[UserId],[Category],[Mobile],[SmsCode],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@Category,@Mobile,@SmsCode,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Id, o.UserId, o.Category, o.Mobile, o.SmsCode, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template GetSmsValidateLog(string smsCode, string mobile, int? category = null, long? userId = null)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.ExtraSchema.Table.SmsValidateLog)
                    .Where("[SmsCode] = @SmsCode", new { smsCode })
                    .Where("[Mobile]=@Mobile", new { mobile })
                    .Where("[CreatedOn]>dateadd(n,-30,getdate())")
                    .OrderBy("CreatedOn DESC");
                if (userId.HasValue)
                {
                    builder.Where("[UserId]=@UserId", new { userId });
                }
                if (category.HasValue)
                {
                    builder.Where("[Category]=@Category", new { category });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            #endregion

            #region Location
            internal static SqlBuilder.Template GetLocations()
            {
                var builder = new SqlBuilder()
                   .Field("*")
                   .Table(DatabaseSchema.ExtraSchema.Table.Location)
                   .OrderBy("Id ASC");
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            #endregion
        }
    }
}